package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 瀵煎嚭Excel2003浠ュ墠(鍖呮嫭2003)鐨勭増鏈� 鎵╁睍鍚嶆槸.xls
 * 鏈�澶ф敮鎸佹暟鎹褰�65500
 */
public class ExcelHSUtils {

    public static void main(String[] args) throws Exception {
        FileInputStream fileInputStream = new FileInputStream(new File("F:\\test\\api_auto_v4\\src\\test\\resources\\case_v5.xls"));
        List<List<Map<String, Object>>> maps = readExcel2003All(fileInputStream);
        //刚加个true是因为我以为流被关了，没想到是我count用了个<
        maps.forEach(t->{
        	System.out.println("sheet");
        	t.forEach(t2->{
        		System.out.println(t2);
        	});
        });
    }


    private ExcelHSUtils() {}

    private static final int MAX_SIZE = 65500;

    /**
//     * @param output
//     * @param sheetName sheet鍚�
//     * @param dataList 鏁版嵁闆嗗悎
//     * @param headers 琛ㄥご
//     * @param attrNames 灞炴�у悕
//     */
//    public static void export(OutputStream output, String sheetName, List<?> dataList, String[] headers, String[] attrNames) throws Exception {
//        export(output, sheetName, dataList, headers, attrNames);
//    }
//
//
//    /**
//     * 鑾峰彇涓�涓狤xcel宸ヤ綔绨�,鍒涘缓澶氫釜sheet椤�
//     *
//     * @param workbookDTOS sheet椤甸泦鍚�
//     * @return workbook
//     */
//    public static HSSFWorkbook exportWorkbookAndSheets(HttpServletResponse response,String fileName, List<WorkbookDTO> workbookDTOS) throws Exception {
//        HSSFWorkbook workbook=new HSSFWorkbook();
//        String excelName = new String(fileName.getBytes("gbk"), "iso8859-1") + "_" + ".xls";
//        response.setContentType("application/vnd.ms-excel");
//        response.addHeader("Content-Disposition", "attachment; filename=" + excelName);
//        for (int i = 0; i < workbookDTOS.size(); i++) {
//            WorkbookDTO workbookDTO = workbookDTOS.get(i);
//            HSSFSheet sheet = workbook.createSheet(workbookDTO.getSheetName());
//            try {
//                workbook.setSheetName(i, workbookDTO.getSheetName());
//                exportSheet(sheet, workbookDTO.getDataList(), workbookDTO.getHeaders(), workbookDTO.getAttrNames());
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
//        workbook.write(response.getOutputStream());
//        response.getOutputStream().flush();
//        response.getOutputStream().close();
//        return workbook;
//    }
//
//
//    /**
//     * Sheet绠�鍗曞啓鍏ユ暟鎹�
//     *
//     * @param sheet 瑕佸啓鍏ユ暟鎹甋heet
//     * @param dataList 鏁版嵁闆嗗悎
//     * @param headers 琛ㄥご
//     * @param attrNames 灞炴�у悕
//     */
//    public static void exportSheet(Sheet sheet, List<?> dataList, String[] headers, String[] attrNames) throws Exception {
//        if (sheet == null) {
//            throw new IllegalArgumentException("sheet鎴栨暟鎹负绌�");
//        }
//        if (sheet instanceof HSSFSheet && dataList != null && dataList.size() > MAX_SIZE) {
//            throw new Exception("鏁版嵁閲忓ぇ浜�" + MAX_SIZE);
//        }
//
//        CellStyle headerStyle = createHeaderCellStyle(sheet.getWorkbook());
//        CellStyle bodyStyle = createBodyCellStyle(sheet.getWorkbook());
//        sheet.setDefaultColumnWidth(24); // 榛樿鍒楀
//
//        int index = 0;
//        Row row = null;
////        if (!CollectionUtils.isEmpty(titleList)) {
////            row = createTitleRow(sheet, titleList, headerStyle, index++); // 澶ф爣棰�
////        }
//
//        row = createRow(sheet, headers, headerStyle, index++); // 琛ㄥご
//
//        if (dataList == null || dataList.size() == 0) {
//            return;
//        }
//
//        boolean isMap = dataList.get(0) instanceof Map; // 鏄惁涓簃ap
//        String[] methodNames = null; // 鏂规硶鍚嶆暟缁�
//        if (!isMap) {
//            methodNames = new String[attrNames.length];
//        }
//
//        for (int i = 0; i < dataList.size(); i++) {
//            row = sheet.createRow(i + index);
//            row.setHeight((short) 300);  // 琛岄珮
//            Object value = null;
//            Object obj = dataList.get(i);
//
//            for (int j = 0; j < attrNames.length; j++) {
//                if (isMap) {
//                    value =((Map<?, ?>)obj).get(attrNames[j]);
//                } else {
//                    if (StringUtils.isBlank(methodNames[j])) {
//                        methodNames[j] = "get" + Character.toUpperCase(attrNames[j].charAt(0)) + attrNames[j].substring(1);
//                    }
//
//                    Method method = obj.getClass().getMethod(methodNames[j]);
//                    value = method.invoke(obj);
//                }
//
//                Cell cell = row.createCell(j);
//                cell.setCellStyle(bodyStyle);
//
//                if (value != null) {
////                    if (valueDTO != null) {
////                        value = processValue(valueDTO, value, j);
////                    }
//
//                    cell.setCellValue(value.toString());
//                }
//
////                if (valueDTO != null && valueDTO.totalContains(j)) {
////                    valueDTO.sum(j, cell.getStringCellValue()); // 鍚堣
////                }
//            }
//
////            if (valueDTO != null && valueDTO.isTotal()) {
////                createRow(sheet, getTotalRow(valueDTO.getTotal()), bodyStyle, dataList.size() + index); // 鐢熸垚鍚堣琛�
////            }
//        }
//    }


    private static String[] getTotalRow(Number[] total) {
        String[] lastRow = new String[total.length];

        for (int i = 0; i < total.length; i++) {
            if (total[i] != null) {
                lastRow[i] = total[i].toString();
            } else if (0 == i) {
                lastRow[i] = "鍚堣锛�";
            } else {
                lastRow[i] = "";
            }
        }

        return lastRow;
    }


    public static Row createRow(Sheet sheet, String[] data, CellStyle style, int rowNum) {
        Row row = sheet.createRow(rowNum);
        row.setHeight((short) 400);

        for (int i = 0; i < data.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(data[i]);
        }

        return row;
    }

//    public static CellStyle createHeaderCellStyle(Workbook workbook) {
//        CellStyle style = workbook.createCellStyle();
//        style.setAlignment(CellStyle.ALIGN_CENTER);
//        style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
//
//        Font font = workbook.createFont();
//        font.setFontHeightInPoints((short) 12);
//        font.setBoldweight((short) 12);
//        font.setFontName("瀹嬩綋");
//
//        style.setFont(font);
//        return style;
//    }


//    public static CellStyle createBodyCellStyle(Workbook workbook) {
//        CellStyle style = workbook.createCellStyle();
//        style.setAlignment(CellStyle.ALIGN_CENTER);
//        style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
//
//        Font font = workbook.createFont();
//        font.setFontHeightInPoints((short) 11);
//        font.setFontName("瀹嬩綋");
//
//        style.setFont(font);
//        return style;
//    }

    private static String formatDate(long time, String pattern) {
        SimpleDateFormat format = new SimpleDateFormat(pattern);

        return format.format(new Date(time));
    }

    /**
     * 读取全部的sheet页到list
     * @param inputStream
     * @return
     * @throws Exception
     */
    public static List<List<Map<String, Object>>> readExcel2003All(InputStream inputStream) throws Exception {
    	HSSFWorkbook work= new HSSFWorkbook(inputStream);
    	int count=work.getActiveSheetIndex();
    	List<List<Map<String, Object>>> list=new ArrayList();
    	for(int i=0;i<=count;i++) {
    		list.add(readExcel2003(work, i));
    	}
    	return list;
    }

    /**
     * 读取单个sheet页到list 2003
     *
     * @param workbook 文件流 new HSSFWorkbook(inputStream)
     * @param sheeti 指定sheet的页数，0开始
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> readExcel2003(HSSFWorkbook workbook,int sheeti) throws Exception {
        int sheetnum=sheeti,startrow=0,startcol=0;
        List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(sheetnum);                     //sheet 浠�0寮�濮�
            int rowNum = sheet.getLastRowNum() + 1;                     //鍙栧緱鏈�鍚庝竴琛岀殑琛屽彿
            Map<String, Object> Header=new HashMap<String, Object>();   //琛ㄥご鐨勪笅鏍囧拰鍐呭
            for (int i = startrow; i < rowNum; i++) {                    //琛屽惊鐜紑濮�
                Map<String, Object> map=null;//姣忎竴琛屾暟鎹殑map
                HSSFRow row = sheet.getRow(i);                             //琛�
                if(row==null) break; //涓棿濡傛灉鏈夌┖琛岋紝鍒欓��鍑�
                int cellNum = row.getLastCellNum();                     //姣忚鐨勬渶鍚庝竴涓崟鍏冩牸浣嶇疆
                if(i!=0){//璁板綍鏁版嵁鐨勮鍙�
                    map=new HashMap<String, Object>();
                    map.put("lineNumber", i+1);
                }
                for (int j = startcol; j < cellNum; j++) {               //鍒楀惊鐜紑濮�
                    HSSFCell cell2 = row.getCell(j);
                    //HSSFCell cell = row.getCell(Short.parseShort(j + ""));
                    //String cellValue = getCellValue(cell, format);
                    String cellValue = getCellValue2003(cell2);
                    //濡傛灉褰撳墠琛岀殑绗竴鍒椾负绌�,鍒欒烦杩囪琛�
                    /*if(j==0 && ("".equals(cellValue)||null==cellValue)){
                    	break;
                    }*/
                    if(i==0){
                        Header.put(j+"", cellValue);
                    }else{
                        map.put((String)Header.get(j+""), cellValue);
                    }
                }
                //姣忎竴琛屾暟鎹殑杩囨护,濡傛灉涓�琛屼腑鎵�鏈夊垪閮戒负null,鍒欎笉鐢熸垚璇ヨ鏁版嵁,
                if(null!=map){
                    Collection<Object> values = map.values();
                    Set<String> keySet = map.keySet();
                    long count2 = keySet.stream().filter(o -> !o.equals("lineNumber")).count();//鎬绘潯鏁�
                    long count = values.stream().filter(o -> null==o).count();//涓虹┖鐨勬潯鏁�
                    if(count<count2){
                        list.add(map);
                    }
                }
            }
            workbook.cloneSheet(sheeti);
        } catch (Exception e) {
            throw e;
        }
        return list;
    }

    /**
     * 瑕佹眰excel鐗堟湰鍦�2007
     *
     * @param inputStream 鏂囦欢淇℃伅
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> readExcel2007(InputStream inputStream) throws Exception {
        int sheetnum=0,startrow=0,startcol=0;
        List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(sheetnum);                     //sheet 浠�0寮�濮�
            int rowNum = sheet.getLastRowNum() + 1;                     //鍙栧緱鏈�鍚庝竴琛岀殑琛屽彿
            Map<String, Object> Header=new HashMap<String, Object>();   //琛ㄥご鐨勪笅鏍囧拰鍐呭
            for (int i = startrow; i < rowNum; i++) {                    //琛屽惊鐜紑濮�
                Map<String, Object> map=null;//姣忎竴琛屾暟鎹殑map
                XSSFRow row = sheet.getRow(i);                             //琛�
                if(row==null) break; //涓棿濡傛灉鏈夌┖琛岋紝鍒欓��鍑�
                int cellNum = row.getLastCellNum();                     //姣忚鐨勬渶鍚庝竴涓崟鍏冩牸浣嶇疆
                if(i!=0){//璁板綍鏁版嵁鐨勮鍙�
                    map=new HashMap<String, Object>();
                    map.put("lineNumber", i+1);
                }
                for (int j = startcol; j < cellNum; j++) {               //鍒楀惊鐜紑濮�
                    XSSFCell cell2 = row.getCell(j);
                    //HSSFCell cell = row.getCell(Short.parseShort(j + ""));
                    //String cellValue = getCellValue(cell, format);
                    String cellValue = getCellValue2007(cell2);
                    //濡傛灉褰撳墠琛岀殑绗竴鍒椾负绌�,鍒欒烦杩囪琛�
                    /*if(j==0 && ("".equals(cellValue)||null==cellValue)){
                    	break;
                    }*/
                    if(i==0){
                        Header.put(j+"", cellValue);
                    }else{
                        map.put((String)Header.get(j+""), cellValue);
                    }
                }
                //姣忎竴琛屾暟鎹殑杩囨护,濡傛灉涓�琛屼腑鎵�鏈夊垪閮戒负null,鍒欎笉鐢熸垚璇ヨ鏁版嵁,
                if(null!=map){
                    Collection<Object> values = map.values();
                    Set<String> keySet = map.keySet();
                    long count2 = keySet.stream().filter(o -> !o.equals("lineNumber")).count();//鎬绘潯鏁�
                    long count = values.stream().filter(o -> null==o).count();//涓虹┖鐨勬潯鏁�
                    if(count<count2){
                        list.add(map);
                    }
                }
            }
            workbook.cloneSheet(0);
        } catch (Exception e) {
            throw e;
        }
        return list;
    }



    /**
     * 鏍规嵁excel鍗曞厓鏍肩被鍨嬭幏鍙杄xcel鍗曞厓鏍煎��
     * @param cell
     * @return
     */
    private static String getCellValue2003(HSSFCell cell) {
        String cellvalue = null;
        if (cell != null) {
            // 鍒ゆ柇褰撳墠Cell鐨凾ype
            switch (cell.getCellType()) {
                // 濡傛灉褰撳墠Cell鐨凾ype涓篘UMERIC
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    short format = cell.getCellStyle().getDataFormat();
                    if(format == 14 || format == 31 || format == 57 || format == 58){   //excel涓殑鏃堕棿鏍煎紡
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                        cellvalue = sdf.format(date);
                    }
                    // 鍒ゆ柇褰撳墠鐨刢ell鏄惁涓篋ate
                    else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //鍏堟敞閲婃棩鏈熺被鍨嬬殑杞崲锛屽湪瀹為檯娴嬭瘯涓彂鐜癏SSFDateUtil.isCellDateFormatted(cell)鍙瘑鍒�2014/02/02杩欑鏍煎紡銆�
                        // 濡傛灉鏄疍ate绫诲瀷鍒欙紝鍙栧緱璇ell鐨凞ate鍊�           // 瀵�2014-02-02鏍煎紡璇嗗埆涓嶅嚭鏄棩鏈熸牸寮�
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue= formater.format(date);
                    } else { // 濡傛灉鏄函鏁板瓧
                        // 鍙栧緱褰撳墠Cell鐨勬暟鍊�
                        cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());

                    }
                    break;
                }
                // 濡傛灉褰撳墠Cell鐨凾ype涓篠TRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 鍙栧緱褰撳墠鐨凜ell瀛楃涓�
                    cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                    break;
                case  HSSFCell.CELL_TYPE_BLANK:
                    cellvalue = null;
                    break;
                // 榛樿鐨凜ell鍊�
                default:{
                    cellvalue = "";
                }
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
    private static String getCellValue2007(XSSFCell cell) {
        String cellvalue = null;
        if (cell != null) {
            // 鍒ゆ柇褰撳墠Cell鐨凾ype
            switch (cell.getCellType()) {
                // 濡傛灉褰撳墠Cell鐨凾ype涓篘UMERIC
                case XSSFCell.CELL_TYPE_NUMERIC: {
                    short format = cell.getCellStyle().getDataFormat();
                    if(format == 14 || format == 31 || format == 57 || format == 58){   //excel涓殑鏃堕棿鏍煎紡
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                        cellvalue = sdf.format(date);
                    }
                    // 鍒ゆ柇褰撳墠鐨刢ell鏄惁涓篋ate
                    else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //鍏堟敞閲婃棩鏈熺被鍨嬬殑杞崲锛屽湪瀹為檯娴嬭瘯涓彂鐜癤SSFDateUtil.isCellDateFormatted(cell)鍙瘑鍒�2014/02/02杩欑鏍煎紡銆�
                        // 濡傛灉鏄疍ate绫诲瀷鍒欙紝鍙栧緱璇ell鐨凞ate鍊�           // 瀵�2014-02-02鏍煎紡璇嗗埆涓嶅嚭鏄棩鏈熸牸寮�
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue= formater.format(date);
                    } else { // 濡傛灉鏄函鏁板瓧
                        // 鍙栧緱褰撳墠Cell鐨勬暟鍊�
                        cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());

                    }
                    break;
                }
                // 濡傛灉褰撳墠Cell鐨凾ype涓篠TRIN
                case XSSFCell.CELL_TYPE_STRING:
                    // 鍙栧緱褰撳墠鐨凜ell瀛楃涓�
                    cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                    break;
                case  XSSFCell.CELL_TYPE_BLANK:
                    cellvalue = null;
                    break;
                // 榛樿鐨凜ell鍊�
                default:{
                    cellvalue = "";
                }
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
}

